package it.polimi.comorazio.data;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.provider.BaseColumns;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	// Tag just for the LogCat window
	private static String TAG = DataBaseHelper.class.getSimpleName();
	public static final int DATABASE_VERSION = 1;
	// Destination path of the database on device
	private static String DB_PATH = "";
	public static final String DB_NAME = "como-razio.db";
	private SQLiteDatabase mDataBase;
	private final Context mContext;

	public class Architect implements BaseColumns {
		public static final String TABLE_NAME = "architects";
		public static final String COLUMN_BORN = "born";
		public static final String COLUMN_DIED = "died";
		public static final String COLUMN_BIOGRAPHY = "summary";
		public static final String COLUMN_NAME = "name";
		public static final String COLUMN_MORE_INFO_LINK = "info_link";

		private Architect() {
		}
	}

	public class PointOfInterest implements BaseColumns {
		public static final String TABLE_NAME = "pointsofinterest";
		public static final String COLUMN_ADDRESS = "address";
		public static final String COLUMN_LONGITUDE = "longitude";
		public static final String COLUMN_LATITUDE = "latitude";
		public static final String COLUMN_DESCRIPTION = "description";
		public static final String COLUMN_NAME = "name";
		public static final String COLUMN_YEAR = "year";

		private PointOfInterest() {
		}
	}

	public class ArchitectsImages {
		public static final String TABLE_NAME = "architects_images";
		public static final String COLUMN_ARCHITECT_ID = "architect_id";
		public static final String COLUMN_IMAGE_NAME = "image_name";

		private ArchitectsImages() {
		}
	}

	public class PointsOfInterestImages {
		public static final String TABLE_NAME = "pointsofinterest_images";
		public static final String COLUMN_POINTOFINTEREST_ID = "pointofinterest_id";
		public static final String COLUMN_IMAGE_NAME = "image_name";

		private PointsOfInterestImages() {
		}
	}

	public class PointsOfInterestArchitects {
		public static final String TABLE_NAME = "pointsofinterest_architect";
		public static final String COLUMN_ARCHITECT_ID = "architect_id";
		public static final String COLUMN_POINTOFINTEREST_ID = "pointofinterest_id";

		private PointsOfInterestArchitects() {
		}
	}

	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		mContext = context;
		DB_PATH = Environment.getDataDirectory().getAbsolutePath() + "/data/"
				+ mContext.getPackageName() + "/databases/";
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 */
	public void createDataBase() {
		if (checkDataBase() == true) {
			Log.d(TAG, "Database file already exists");
		} else {
			Log.d(TAG, "Database file doesn't exist");
			try {
				// By calling this method an empty database will be created into
				// the default system path of your application so we are gonna
				// be able to overwrite that database with our database.
				this.getReadableDatabase();
				// Copy the database from assets
				copyDataBase();
			} catch (IOException e) {
				Log.e(TAG, "Error copying database", e);
			} catch (SQLiteException e1) {
				Log.e(TAG, "Error opening database", e1);
			} finally {
				this.close();
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {
		File dbFile = new File(DB_PATH + DB_NAME);
		return dbFile.exists();
	}

	/**
	 * Copies the database from the local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transferring a byte stream.
	 * 
	 * @throws IOException
	 *             In case of any error while handling the streams
	 */
	private void copyDataBase() throws IOException {
		// Open local database as the input stream
		InputStream myInput = mContext.getAssets().open(DB_NAME);
		// Path to the just created empty database
		String outFileName = DB_PATH + DB_NAME;
		// Open the empty database as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);
		// transfer bytes from the input file to the output file
		byte[] mBuffer = new byte[1024];
		int mLength;
		while ((mLength = myInput.read(mBuffer)) > 0) {
			myOutput.write(mBuffer, 0, mLength);
		}
		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
		Log.d(TAG, "Database was created");
	}

	/**
	 * Open the database, so we can query it.
	 * 
	 * @throws SQLException
	 *             if the database cannot be opened
	 */
	public void openDataBase() throws SQLException {
		if (mDataBase == null || (mDataBase != null && !mDataBase.isOpen())) {
			mDataBase = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null,
					SQLiteDatabase.OPEN_READONLY);
			Log.d(TAG, "Database was opened");
		}
	}

	@Override
	public synchronized void close() {
		if (mDataBase != null)
			mDataBase.close();
		super.close();
		Log.d(TAG, "Database was closed");
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// May induce an infinite loop if the getReadableDatabase or
		// getWritableDatabase calls onCreate
		// this.createDataBase();
		// this.openDataBase();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// May induce an infinite loop if the getReadableDatabase or
		// getWritableDatabase calls onCreate
		// if (checkDataBase() == true) {
		// // If exist then reload it from assets file
		// File dbFile = new File(DB_PATH + DB_NAME);
		// if (dbFile.delete() == true) {
		// Log.d(TAG, "Database was deleted");
		// createDataBase();
		// } else {
		// Log.e(TAG, "Error while deleting database file on device ["
		// + dbFile.getAbsolutePath() + "]");
		// }
		// } else {
		// // Doesn't it exist yet? Then lets create it
		// createDataBase();
		// }
	}

	public Cursor getAllArchitects() {
		String query = "select * from " + Architect.TABLE_NAME + " where " + Architect.COLUMN_BORN + "!='' order by "
				+ Architect._ID + " asc";
		Cursor c = mDataBase.rawQuery(query, null);
		Log.d(TAG, "Got [" + String.valueOf(c.getCount())
				+ "] results executing query [" + query + "]");
		return c;
	}

	public Cursor getArchitectById(int architectId) {
		String[] selectionArgs = { String.valueOf(architectId) };
		String query = "select * from " + Architect.TABLE_NAME + " where "
				+ Architect._ID + "=?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public Cursor getAllPointsOfInterest() {
		String query = "select * from " + PointOfInterest.TABLE_NAME;
		Cursor c = mDataBase.rawQuery(query, null);
		Log.d(TAG, "Got [" + String.valueOf(c.getCount())
				+ "] results executing query [" + query + "]");
		return c;
	}

	public Cursor getPointOfInterestById(int pointOfInterestId) {
		String[] selectionArgs = { String.valueOf(pointOfInterestId) };
		String query = "select * from " + PointOfInterest.TABLE_NAME
				+ " where " + PointOfInterest._ID + "=?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public Cursor getImagesOfArchitectById(int architectId) {
		String[] selectionArgs = { String.valueOf(architectId) };
		String query = "select I.*  from " + Architect.TABLE_NAME + " A,"
				+ ArchitectsImages.TABLE_NAME + " I where A." + Architect._ID
				+ "=I." + ArchitectsImages.COLUMN_ARCHITECT_ID + " and A."
				+ Architect._ID + "=?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public Cursor getImagesOfPointOfInterestById(int pointOfInterestId) {
		String[] selectionArgs = { String.valueOf(pointOfInterestId) };
		String query = "select I.* from " + PointOfInterest.TABLE_NAME + " P,"
				+ PointsOfInterestImages.TABLE_NAME + " I where P."
				+ PointOfInterest._ID + "=I."
				+ PointsOfInterestImages.COLUMN_POINTOFINTEREST_ID + " and P."
				+ PointOfInterest._ID + "=?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public Cursor getPointsOfInterestByArchitectId(int architectId) {
		String[] selectionArgs = { String.valueOf(architectId) };
		String query = "select P.* from "
				+ PointsOfInterestArchitects.TABLE_NAME + " PA, "
				+ Architect.TABLE_NAME + " A, " + PointOfInterest.TABLE_NAME
				+ " P where PA."
				+ PointsOfInterestArchitects.COLUMN_ARCHITECT_ID + "=A."
				+ Architect._ID + " and PA."
				+ PointsOfInterestArchitects.COLUMN_POINTOFINTEREST_ID + "=P."
				+ PointOfInterest._ID + " and A." + Architect._ID + "= ?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public Cursor getArchitectsByPointOfInterestId(int poiId) {
		String[] selectionArgs = { String.valueOf(poiId) };
		String query = "select A.* from "
				+ PointsOfInterestArchitects.TABLE_NAME + " PA, "
				+ Architect.TABLE_NAME + " A, " + PointOfInterest.TABLE_NAME
				+ " P where PA."
				+ PointsOfInterestArchitects.COLUMN_ARCHITECT_ID + "=A."
				+ Architect._ID + " and PA."
				+ PointsOfInterestArchitects.COLUMN_POINTOFINTEREST_ID + "=P."
				+ PointOfInterest._ID + " and P." + PointOfInterest._ID + "= ?";
		Cursor c = mDataBase.rawQuery(query, selectionArgs);
		Log.d(TAG,
				"Got [" + String.valueOf(c.getCount())
						+ "] results executing query ["
						+ query.replace("?", selectionArgs[0]) + "]");
		return c;
	}

	public static String getCurrentLocale(Context c) {
		String lang = c.getResources().getConfiguration().locale.getLanguage();
		if ("".compareTo(lang) == 0) {
			// Watch out here !! Hard-coded string in case no language is
			// retrieved
			lang = "en";
		}
		return "_" + lang;
	}
}
